package cn.imvc.data.jpa.repository;

import java.io.Serializable;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Order;
import org.springframework.data.jpa.repository.support.JpaEntityInformation;
import org.springframework.data.jpa.repository.support.JpaEntityInformationSupport;
import org.springframework.data.jpa.repository.support.SimpleJpaRepository;

import cn.imvc.data.domain.ViewRequest;
import cn.imvc.data.util.JpqlUtil;
import cn.imvc.data.util.PageableUtil;

public class ISimpleJpaRepository<T, ID extends Serializable> extends
		SimpleJpaRepository<T, ID> implements IJpqlExecutor<T>, ISqlExecutor<T> {
	private JpaEntityInformation<T, ?> entityInfo;
	private EntityManager entityManager;

	public ISimpleJpaRepository(Class<T> domainClass, EntityManager em) {
		this(JpaEntityInformationSupport.getEntityInformation(domainClass, em),
				em);
	}

	public ISimpleJpaRepository(JpaEntityInformation<T, ?> entityInformation,
			EntityManager entityManager) {
		super(entityInformation, entityManager);
		this.entityInfo = entityInformation;
		this.entityManager = entityManager;
	}

	public Page<T> findAll(ViewRequest viewRequest) {
		if (viewRequest == null)
			viewRequest = new ViewRequest();
		return getPageImpl(viewRequest.getParams(),
				PageableUtil.toPageable(viewRequest));
	}

	private Page<T> getPageImpl(Map<String, String> params, Pageable pageable) {
		String jpql = "from " + entityInfo.getEntityName()
				+ JpqlUtil.toWhere(params);
		
		if(sqlValidate(jpql)){
			return null;
		}
		String orderBy = "";
		if (pageable.getSort() != null) {
			orderBy = " order by "
					+ pageable.getSort().toString().replaceAll(":", "");
		}
		Query query = entityManager.createQuery(jpql + orderBy);
		query.setFirstResult((pageable.getPageNumber())
				* pageable.getPageSize());
		query.setMaxResults(pageable.getPageSize());
		Long total = ((Long) entityManager
				.createQuery("select count(*) " + jpql)
				.setHint("org.hibernate.cacheable", true).getSingleResult())
				.longValue();

		query.setHint("org.hibernate.cacheable", true);
		@SuppressWarnings("unchecked")
		List<T> content = query.getResultList();
		return new PageImpl<T>(content, pageable, total);
	}

	public Page<T> findAll(String select, String from, ViewRequest viewRequest) {
		return getPageObject(select, from, viewRequest.getParams(),
				PageableUtil.toPageable(viewRequest));
	}

	private Page<T> getPageObject(String select, String from,
			Map<String, String> params, Pageable pageable) {
		String jpql = " from " + from + JpqlUtil.toWhere(params);
		if(sqlValidate(select+jpql)){
			return null;
		}
		String orderBy = "";
		if (pageable.getSort() != null) {
			orderBy = " order by "
					+ pageable.getSort().toString().replaceAll(":", "");
		}
		Query query = entityManager.createQuery("select " + select + jpql
				+ orderBy);
		query.setFirstResult((pageable.getPageNumber())
				* pageable.getPageSize());
		query.setMaxResults(pageable.getPageSize());
		Long total = ((Long) entityManager.createQuery(
				"select count(" + select + ") " + jpql).getSingleResult())
				.longValue();
		@SuppressWarnings("unchecked")
		List<T> content = query.getResultList();
		return new PageImpl<T>(content, pageable, total);
	}

	public List<T> findAllJpql(String jpql) {
		if(sqlValidate(jpql)){
			return null;
		}
		jpql=jpql.replaceAll(".*([;]+|(--)+).*", " ");
		Query query = entityManager.createQuery(jpql);
		@SuppressWarnings("unchecked")
		List<T> content = query.getResultList();
		return content;
	}

	public List<Object[]> findAllJpqlArray(String jpql) {
		if(sqlValidate(jpql)){
			return null;
		}
		jpql=jpql.replaceAll(".*([;]+|(--)+).*", " ");
		Query query = entityManager.createQuery(jpql);
		@SuppressWarnings("unchecked")
		List<Object[]> content = query.getResultList();
		return content;
	}

	public List<T> findAllJpql(String jpql, Sort sort) {
		if(sqlValidate(jpql)){
			return null;
		}
		jpql=jpql.replaceAll(".*([;]+|(--)+).*", " ");
		String orderBy = "";
		if (sort != null) {
			orderBy = " order by " + sort.toString().replaceAll(":", "");
		}
		Query query = entityManager.createQuery(jpql + orderBy);
		@SuppressWarnings("unchecked")
		List<T> content = query.getResultList();
		return content;
	}

	public List<Object[]> findAllJpqlArray(String jpql, Sort sort) {
		if(sqlValidate(jpql)){
			return null;
		}
		jpql=jpql.replaceAll(".*([;]+|(--)+).*", " ");
		String orderBy = "";
		if (sort != null) {
			orderBy = " order by " + sort.toString().replaceAll(":", "");
		}
		Query query = entityManager.createQuery(jpql + orderBy);
		@SuppressWarnings("unchecked")
		List<Object[]> content = query.getResultList();
		return content;
	}

	public Page<T> findAllJpql(String select, String from, String where,
			Pageable pageable) {
		select=select.replaceAll(".*([;]+|(--)+).*", " ");
		
		from=select.replaceAll(".*([;]+|(--)+).*", " ");
		
		where=where.replaceAll(".*([;]+|(--)+).*", " ");
		String jpql = " from " + from + " where " + where;
		
		if(sqlValidate(select+jpql)){
			return null;
		}
		String orderBy = "";
		if (pageable.getSort() != null) {
			orderBy = " order by "
					+ pageable.getSort().toString().replaceAll(":", "");
		}
		Query query = entityManager.createQuery("select " + select + jpql
				+ orderBy);
		query.setFirstResult((pageable.getPageNumber())
				* pageable.getPageSize());
		query.setMaxResults(pageable.getPageSize());
		Long total = ((Long) entityManager.createQuery(
				"select count(" + select + ") " + jpql).getSingleResult())
				.longValue();
		@SuppressWarnings("unchecked")
		List<T> content = query.getResultList();
		return new PageImpl<T>(content, pageable, total);
	}

	public Page<Object[]> findAllJpqlArray(String select, String from,
			String where, Pageable pageable) {
        select=select.replaceAll(".*([;]+|(--)+).*", " ");
		
		from=select.replaceAll(".*([;]+|(--)+).*", " ");
		
		where=where.replaceAll(".*([;]+|(--)+).*", " ");
		String jpql = " from " + from + " where " + where;
		
		if(sqlValidate(select+jpql)){
			return null;
		}
		String orderBy = "";
		if (pageable.getSort() != null) {
			orderBy = " order by "
					+ pageable.getSort().toString().replaceAll(":", "");
		}
		Query query = entityManager.createQuery("select " + select + jpql
				+ orderBy);
		query.setFirstResult((pageable.getPageNumber())
				* pageable.getPageSize());
		query.setMaxResults(pageable.getPageSize());
		Long total = ((Long) entityManager.createQuery(
				"select count(" + select + ") " + jpql).getSingleResult())
				.longValue();
		@SuppressWarnings("unchecked")
		List<Object[]> content = query.getResultList();
		return new PageImpl<Object[]>(content, pageable, total);
	}
	
	public List<Object[]> findAllSql(String sql) {
		if(sqlValidate(sql)){
			return null;
		}
		sql=sql.replaceAll(".*([;]+|(--)+).*", " ");
		Query query = entityManager.createNativeQuery(sql);
		@SuppressWarnings("unchecked")
		List<Object[]> objecArraytList = query.getResultList();
		return objecArraytList;
	}

	public <E> List<E> findAllSql(String sql, Class<E> clazz) {
		if(sqlValidate(sql)){
			return null;
		}
		sql=sql.replaceAll(".*([;]+|(--)+).*", " ");
		Query query = entityManager.createNativeQuery(sql, clazz);
		@SuppressWarnings("unchecked")
		List<E> objecList = query.getResultList();
		return objecList;
	}

	public List<T> findAllSql(String sql, Sort sort,Class<T> clazz) {
		if(sqlValidate(sql)){
			return null;
		}
		sql=sql.replaceAll(".*([;]+|(--)+).*", " ");
		String orderBy = "";
		if (sort != null) {
			orderBy = " order by " + sort.toString().replaceAll(":", "");
		}
		Query query = entityManager.createNativeQuery(sql + orderBy,clazz);
		@SuppressWarnings("unchecked")
		List<T> content = query.getResultList();
		return content;
	}

	public List<Object[]> findAllSqlArray(String sql, Sort sort) {
		if(sqlValidate(sql)){
			return null;
		}
		sql=sql.replaceAll(".*([;]+|(--)+).*", " ");
		String orderBy = "";
		if (sort != null) {
			orderBy = " order by " + sort.toString().replaceAll(":", "");
		}
		Query query = entityManager.createNativeQuery(sql + orderBy);
		@SuppressWarnings("unchecked")
		List<Object[]> content = query.getResultList();
		return content;
	}

	public Page<T> findAllSql(String select, String from, String where,
			Pageable pageable,Class<T> clazz) {
		
		select=select.replaceAll(".*([;]+|(--)+).*", " ");
		from=from.replaceAll(".*([;]+|(--)+).*", " ");
		where=where.replaceAll(".*([;]+|(--)+).*", " ");
		
		String jpql = " from " + from + " where " + where;
		
		if(sqlValidate(select+jpql)){
			return null;
		}
		String orderBy = "";
		if (pageable.getSort() != null) {
			orderBy = " order by "
					+ pageable.getSort().toString().replaceAll(":", "");
		}
		Query query = entityManager.createNativeQuery("select " + select + jpql
				+ orderBy, clazz);
		query.setFirstResult((pageable.getPageNumber())
				* pageable.getPageSize());
		query.setMaxResults(pageable.getPageSize());
		Long total = ((Long) entityManager.createNativeQuery(
				"select count(" + select + ") " + jpql).getSingleResult())
				.longValue();
		@SuppressWarnings("unchecked")
		List<T> content = query.getResultList();
		return new PageImpl<T>(content, pageable, total);
	}

	public Page<Object[]> findAllSqlArray(String select, String from,
			String where, Pageable pageable) {
		
		select=select.replaceAll(".*([;]+|(--)+).*", " ");
		from=from.replaceAll(".*([;]+|(--)+).*", " ");
		where=where.replaceAll(".*([;]+|(--)+).*", " ");
		
		String jpql = " from " + from ;
		
		if(where!=null&&!where.isEmpty()){
			jpql+=" where " + where;
		}
		
		if(sqlValidate(select+jpql)){
			return null;
		}
		String orderBy = "";
		if (pageable.getSort() != null) {
			orderBy = " order by "
					+ pageable.getSort().toString().replaceAll(":", "");
		}
		Query query = entityManager.createNativeQuery("select " + select + jpql
				+ orderBy);
		query.setFirstResult((pageable.getPageNumber())
				* pageable.getPageSize());
		query.setMaxResults(pageable.getPageSize());
		System.out.println("select count(" + select + ") " + jpql);
		
		Long total = ((Long) entityManager.createNativeQuery(
				"select count(" + select + ") " + jpql).getSingleResult())
				.longValue();
		@SuppressWarnings("unchecked")
		List<Object[]> content = query.getResultList();
		return new PageImpl<Object[]>(content, pageable, total);
	}

    private boolean sqlValidate(String sql) {
    	sql = sql.toLowerCase();
        String[] badStrs = {"create ","insert", "update ", "delete","drop","exec","execute","alter","declare","truncate"};
        for (int i = 0; i < badStrs.length; i++) {
            if (sql.indexOf(badStrs[i]) >= 0) {
                return true;
            }
        }
        return false;
    }

	public Page<Object[]> findAllSql(String sql, Pageable pageable) {
		sql=sql.replaceAll(".*([;]+|(--)+).*", " ");
		if(sqlValidate(sql)){
			return null;
		}
//		String orderBy = "";
//		if (pageable.getSort() != null) {
//			orderBy = " order by "
//					+ pageable.getSort().toString().replaceAll(":", "");
//		}
		Query query = entityManager.createNativeQuery(sql);
		query.setFirstResult((pageable.getPageNumber())
				* pageable.getPageSize());
		query.setMaxResults(pageable.getPageSize());
		
		Long total = ((BigInteger) entityManager.createNativeQuery(
				"select count(*) from ( "+sql+" ) t").getSingleResult())
				.longValue();
		
		@SuppressWarnings("unchecked")
		List<Object[]> content = query.getResultList();
		return new PageImpl<Object[]>(content, pageable, total);
	}

	public <E> Page<E> findAllSql(String sql, Pageable pageable, Class<E> clazz) {
		sql=sql.replaceAll(".*([;]+|(--)+).*", " ");
		if(sqlValidate(sql)){
			return null;
		}
//		String orderBy = "";
//		if (pageable.getSort() != null) {
//			orderBy = " order by "
//					+ pageable.getSort().toString().replaceAll(":", "");
//		}
		Query query = entityManager.createNativeQuery(sql, clazz);
		
		query.setFirstResult((pageable.getPageNumber())
				* pageable.getPageSize());
		query.setMaxResults(pageable.getPageSize());
		Long total = ((BigInteger) entityManager.createNativeQuery(
				"select count(*) from ( "+sql+" ) t").getSingleResult())
				.longValue();
		@SuppressWarnings("unchecked")
		List<E> content = query.getResultList();
		return new PageImpl<E>(content, pageable, total);
	}


	public Page<Object[]> findPageResultBySql(String sql, Object[] args, Pageable pageable) {
		String countSql = "select count(1) from ("+sql+") t";
		String selectCols = sql.substring(sql.indexOf("select")+6,sql.indexOf("from")).toLowerCase();
		Query countQuery = entityManager.createNativeQuery(countSql);
		Sort sort =pageable.getSort();
		if(sort!=null){
			Iterator<Order> iterators = sort.iterator();
			if(iterators.hasNext()){
				sql+=" order by ";
			}
			while(iterators.hasNext()){
				Order order = iterators.next();
				String orderStr = order.getProperty();
				String orderType = order.getDirection().name();
				if(selectCols.contains(orderStr.toLowerCase())){
					sql +=" "+orderStr+" "+orderType+",";
				}
			}
		}
		if(sql.endsWith(",")){//有sort
			sql = sql.substring(0, sql.length()-1);
		}
		Query query = entityManager.createNativeQuery(sql);
		int i = 1;
		if(args!=null){
			for (Object arg : args) {
				query.setParameter(i, arg);
				countQuery.setParameter(i, arg);
				i++;
			}
		}
		query.setFirstResult(pageable.getPageNumber() * pageable.getPageSize());
		query.setMaxResults(pageable.getPageSize());
		List<Object[]> content = query.getResultList();
		Long total = ((BigInteger)countQuery.getSingleResult()).longValue();
		return new PageImpl<Object[]>(content, pageable, total);
	}

	public void updateSql(String sql) {
			sql=sql.replaceAll(".*([;]+|(--)+).*", " ");
			Query query = entityManager.createNativeQuery(sql);
			query.executeUpdate();
	}
}
